package com.netcracker.training.automonic.dao.entities.oracle;

/**
 * SQL queries for Oracle DB.
 * 
 * @author Vadim Gidulyan
 */

public final class SQLQueries // TODO Javadoc
{

	private static final String SELECT_SITE = 
			"SELECT "
		  + "	site_id, "
		  + "	name "
		  + "FROM "
		  + "	Sites ";
	
	private static final String SELECT_OBJECT = 
			"SELECT "
		  + "	object_id, "
		  + "	site, "
		  + "	name "
		  + "FROM "
		  + "	Objects ";
	
	private static final String SELECT_ATTRIBUTE_TYPE = 
			"SELECT "
		  + "	attrtype_id, "
		  + "	code, "
		  + "	name "
		  + "FROM "
		  + "	AttributeTypes ";
	
	private static final String SELECT_ATTRIBUTE = 
			"SELECT "
		  + "	attr_id, "
		  + "	objectid, "
		  + "	attrtype, "
		  + "	value "
		  + "FROM "
		  + "	Attributes ";
	
	private static final String SELECT_STATISTICS_TYPE = 
			"SELECT "
		  + "	statstype_id, "
		  + "	code, "
		  + "	name "
		  + "FROM "
		  + "	StatsTypes ";
	
	private static final String SELECT_STATISTICS = 
			"SELECT "
		  + "	time, "
		  + "	objectid, "
		  + "	statstype,"
		  + "	value "
		  + "FROM "
		  + "	Statistics ";
	
	public static final String SELECT_SITE_BY_ID = 
			SELECT_SITE
		  + "WHERE"
		  + "	site_id = %d";
	
	public static final String SELECT_SITE_BY_NAME = 
			SELECT_SITE
		  + "WHERE"
		  + "	name = '%s'";
	
	public static final String SELECT_OBJECT_BY_ID = 
			SELECT_OBJECT
		  + "WHERE"
		  + "	object_id = %d";
	
	public static final String SELECT_OBJECT_BY_SITE_NAME = 
			SELECT_OBJECT
		  + "WHERE"
		  + "	site = %d"
		  + "	AND name = '%s'";
	
	public static final String SELECT_OBJECTS_BY_SITE_ID = 
			SELECT_OBJECT
		  + "WHERE"
		  + "	site = %d";
	
	public static final String SELECT_OBJECTS_BY_NAME = 
			SELECT_OBJECT
		  + "WHERE"
		  + "	name = '%s'";
	
	public static final String SELECT_ATTRIBUTE_TYPE_BY_ID = 
			SELECT_ATTRIBUTE_TYPE
		  + "WHERE "
		  + "	attrtype_id = %d";
	
	public static final String SELECT_ATTRIBUTE_TYPE_BY_CODE = 
			SELECT_ATTRIBUTE_TYPE
		  + "WHERE "
		  + "	code = '%s'";
	
	public static final String SELECT_ATTRIBUTE_TYPES_BY_NAME = 
			SELECT_ATTRIBUTE_TYPE
		  + "WHERE "
		  + "	name = '%s'";
	
	public static final String SELECT_ATTRIBUTE_BY_ID = 
			SELECT_ATTRIBUTE
		  + "WHERE "
		  + "	attr_id = %d";
	
	public static final String SELECT_ATTRIBUTE_BY_OBJECT_TYPE = 
			SELECT_ATTRIBUTE
		  + "WHERE "
		  + "	objectid = %d"
		  + "	AND attrtype = %d";
	
	public static final String SELECT_ATTRIBUTES_BY_OBJECT_ID = 
			SELECT_ATTRIBUTE
		  + "WHERE "
		  + "	objectid = %d";
	
	public static final String SELECT_ATTRIBUTES_BY_TYPE_ID = 
			SELECT_ATTRIBUTE
		  + "WHERE "
		  + "	attrtype = %d";
	
	public static final String SELECT_STATISTICS_TYPE_BY_ID = 
			SELECT_STATISTICS_TYPE
		  + "WHERE "
		  + "	statstype_id = %d";
	
	public static final String SELECT_STATISTICS_TYPE_BY_CODE = 
			SELECT_STATISTICS_TYPE
		  + "WHERE "
		  + "	code = '%s'";
	
	public static final String SELECT_STATISTICS_TYPES_BY_NAME = 
			SELECT_STATISTICS_TYPE
		  + "WHERE "
		  + "	name = '%s'";
	
	public static final String SELECT_STATISTICS_BY_TIME_OBJECT_TYPE = 
			SELECT_STATISTICS
		  + "WHERE "
		  + "	TRUNC(time, 'MI') = TO_DATE('%s', 'YYYY-MM-DD HH24:MI') "
		  + "	AND objectid = %d "
		  + "	AND statstype = %d";
	
	public static final String SELECT_STATISTICS_BY_TIME = 
			SELECT_STATISTICS
		  + "WHERE "
		  + "	TRUNC(time, 'MI') = TO_DATE('%s', 'YYYY-MM-DD HH24:MI')";
	
	public static final String SELECT_STATISTICS_BY_OBJECT_ID = 
			SELECT_STATISTICS
		  + "WHERE "
		  + "	objectid = %d";
	
	public static final String SELECT_STATISTICS_BY_TYPE_ID = 
			SELECT_STATISTICS
		  + "WHERE "
		  + "	statstype = %d";
	
	public static final String SELECT_ALL_SITES = 
			SELECT_SITE;
	
	public static final String SELECT_ALL_OBJECTS = 
			SELECT_OBJECT;
	
	public static final String SELECT_ALL_ATTRIBUTE_TYPES = 
			SELECT_ATTRIBUTE_TYPE;
	
	public static final String SELECT_ALL_ATTRIBUTES = 
			SELECT_ATTRIBUTE;
	
	public static final String SELECT_ALL_STATISTICS_TYPES = 
			SELECT_STATISTICS_TYPE;
	
	public static final String SELECT_ALL_STATISTICS = 
			SELECT_STATISTICS;

}
